package dao;

import model.House;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class HouseDao {
    public void addHouse(House house) throws Exception {
        Connection conn = DBUtil.getConnection();
        String sql = "INSERT INTO house (id, area) VALUES (?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, house.getId());
        ps.setDouble(2, house.getArea());
        ps.executeUpdate();
        ps.close();
        conn.close();
    }

    public void updateHouse(House house) throws Exception {
        Connection conn = DBUtil.getConnection();
        String sql = "UPDATE house SET area=? WHERE id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setDouble(1, house.getArea());
        ps.setString(2, house.getId());
        ps.executeUpdate();
        ps.close();
        conn.close();
    }

    public void deleteHouse(String id) throws Exception {
        Connection conn = DBUtil.getConnection();
        String sql = "DELETE FROM house WHERE id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, id);
        ps.executeUpdate();
        ps.close();

        // 删除own表对应关系
        String delOwn = "DELETE FROM own WHERE houseId=?";
        PreparedStatement psDel = conn.prepareStatement(delOwn);
        psDel.setString(1, id);
        psDel.executeUpdate();
        psDel.close();

        conn.close();
    }

    public House getHouseById(String id) throws Exception {
        Connection conn = DBUtil.getConnection();
        String sql = "SELECT * FROM house WHERE id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, id);
        ResultSet rs = ps.executeQuery();
        House house = null;
        if (rs.next()) {
            house = new House(rs.getString("id"), rs.getDouble("area"));
        }
        rs.close();
        ps.close();
        conn.close();
        return house;
    }

    public List<House> getAllHouses() throws Exception {
        Connection conn = DBUtil.getConnection();
        String sql = "SELECT * FROM house";
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        List<House> list = new ArrayList<>();
        while (rs.next()) {
            list.add(new House(rs.getString("id"), rs.getDouble("area")));
        }
        rs.close();
        st.close();
        conn.close();
        return list;
    }

    // 获取房屋所有者姓名列表
    public List<String> getOwnerNamesByHouseId(String houseId) throws Exception {
        Connection conn = DBUtil.getConnection();
        String sql = "SELECT o.name FROM own ow JOIN owner o ON ow.ownerId = o.id WHERE ow.houseId = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, houseId);
        ResultSet rs = ps.executeQuery();
        List<String> names = new ArrayList<>();
        while (rs.next()) {
            names.add(rs.getString("name"));
        }
        rs.close();
        ps.close();
        conn.close();
        return names;
    }
}
